package com.sa.app.utilities;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

import com.sa.app.objects.DatasheetLine;
import com.sa.app.objects.DatasheetLineImpl;
import com.sa.app.objects.Item;
import com.sa.app.objects.ItemImpl;
import com.sa.app.objects.RealService;
import com.sa.app.objects.RealServiceImpl;
import com.sa.app.objects.Region;
import com.sa.app.objects.RegionImpl;
import com.sa.app.objects.VirtualService;
import com.sa.app.objects.VirtualServiceImpl;

public class DataBaseManagerImpl implements DataBaseManager {

	private SQLiteDatabase dataBase;
	DataBaseHelper helper;

	// Hay que especificar si se abre en modo lectura(R) o escritura(W)
	public DataBaseManagerImpl(String mode, Context cont) {

		helper = new DataBaseHelper(cont, "DBSostArch", null, 65);
		if (mode.equals("R")) {
			this.dataBase = helper.getReadableDatabase();
		} else if (mode.equals("W")) {
			this.dataBase = helper.getWritableDatabase();
		} else {
			throw new IllegalArgumentException("Modo invalido, admitido R/W");
		}

	}

	public void open(String mode) {
		if (mode.equals("R")) {
			this.dataBase = helper.getReadableDatabase();
		} else if (mode.equals("W")) {
			this.dataBase = helper.getWritableDatabase();
		} else {
			throw new IllegalArgumentException("Modo invalido, admitido R/W");
		}
	}

	public Boolean isOpen() {
		Boolean res = false;
		if (dataBase != null) {
			res = true;
		}
		return res;
	}

	// Solo para las sentencias sql que no devuelven datos.
	// Insertar/Actualizar/Borrar
	public void execSQL(String s) {
		dataBase.execSQL(s);
	}

	// Para consultar la base de datos
	public Cursor rawQuery(String s) {
		Cursor c = dataBase.rawQuery(s, null);
		return c;
	}

	// Para consultar la base de datos y que te lo devuelva en una lista
	public List<List<String>> query(String s) {
		Cursor c = rawQuery(s);
		List<List<String>> list = new ArrayList<List<String>>();
		if (c.moveToFirst()) {
			do {
				List<String> l1 = new ArrayList<String>();
				for (int i = 0; i < c.getColumnCount(); i++) {
					String str = c.getString(i);
					l1.add(str);
				}
				list.add(l1);
			} while (c.moveToNext());
		}
		return list;
	}

	public List<List<String>> selectRegion(Integer latitude, Integer longitude) {
		String s = "SELECT * FROM regions WHERE latitude=" + latitude
				+ " AND longitude=" + longitude;
		return query(s);
	}

	public List<List<String>> selectItemByNFCID(String NFCID) {
		String s = "SELECT * FROM items WHERE NFCID='" + NFCID + "'";
		return query(s);
	}

	public List<List<String>> selectItemByAliasID(String aliasID) {
		String s = "SELECT * FROM items WHERE aliasID='" + aliasID + "'";
		return query(s);
	}

	public List<List<String>> selectVirtualService(String nameID, String NFCID) {
		String s = "SELECT * FROM virtual_services WHERE nameID='" + nameID
				+ "' AND NFCID='" + NFCID + "'";
		return query(s);
	}

	public List<List<String>> selectRealService(String nameID, String NFCID) {
		String s = "SELECT * FROM virtual_services WHERE nameID='" + nameID
				+ "' AND NFCID='" + NFCID + "'";
		return query(s);
	}

	public List<List<String>> selectHistory(String NFCID) {

		String s = "SELECT u.latitude, u.longitude, r.radio FROM uses u, regions r  WHERE u.NFCID='"
				+ NFCID
				+ "' AND u.latitude=r.latitude AND u.longitude=r.longitude";

		return query(s);
	}

	public List<List<String>> selectUse(String NFCID, Date date) {
		String s = "SELECT * FROM uses WHERE NFCID='" + NFCID + "' AND date="
				+ date.getTime();
		return query(s);
	}

	public Integer getNumUsesFor(String NFCID) {
		String s = "SELECT * FROM uses WHERE NFCID='" + NFCID + "'";
		return query(s).size();
	}

	public void insertRegion(List<String> region) {
		insertRegion(region.get(0), Integer.valueOf(region.get(1)),
				Integer.valueOf(region.get(2)), Integer.valueOf(region.get(3)));
	}

	public void insertRegion(String name, Integer latitude, Integer longitude,
			Integer radio) {
		Integer minGPS = MyApplication.MIN_GPS_ACCURACY.intValue();
		if (radio.compareTo(minGPS) <= 0) {
			radio = Integer.valueOf(minGPS);
		}
		String s = "INSERT OR REPLACE INTO regions (name, latitude, longitude, radio) "
				+ "VALUES ('"
				+ name
				+ "',"
				+ latitude
				+ ", "
				+ longitude
				+ ","
				+ radio + ")";

		dataBase.execSQL(s);

	}

	public void insertItem(List<String> list) {
		insertItem(list.get(0), list.get(1), list.get(2),
				Integer.valueOf(list.get(3)), list.get(4), list.get(5),
				list.get(6), Integer.valueOf(list.get(7)),
				Integer.valueOf(list.get(8)));
	}

	public void insertItem(String NFCID, String aliasID, String XMLURL,
			Integer XMLVersion, String manufacturer, String model,
			String imageUrl, Integer regionLatitude, Integer regionLongitude) {

		String s = "INSERT OR REPLACE INTO items (NFCID, aliasID, XMLURL, XMLVersion, manufacturer, model, imageURL, regionLatitude, regionLongitude) "
				+ "VALUES ('"
				+ NFCID
				+ "','"
				+ aliasID
				+ "','"
				+ XMLURL
				+ "',"
				+ XMLVersion
				+ ",'"
				+ manufacturer
				+ "','"
				+ model
				+ "','"
				+ imageUrl
				+ "',"
				+ regionLatitude
				+ ","
				+ regionLongitude
				+ ")";

		dataBase.execSQL(s);

	}

	public void insertVirtualService(List<List<String>> virtualServices) {
		for (List<String> data : virtualServices) {
			insertVirtualService(data.get(0), data.get(1), data.get(2),
					data.get(3));
		}
	}

	public void insertVirtualService(String nameID, String NFCID,
			String description, String serviceURL) {

		String s = "INSERT OR REPLACE INTO virtual_services (nameID, NFCID, description, serviceURL) "
				+ "VALUES ('"
				+ nameID
				+ "','"
				+ NFCID
				+ "','"
				+ description
				+ "','" + serviceURL + "')";

		dataBase.execSQL(s);

	}

	public void insertRealService(List<List<String>> realServices) {
		for (List<String> data : realServices) {
			insertRealService(data.get(0), data.get(1), data.get(2),
					data.get(3));
		}
	}

	public void insertRealService(String nameID, String NFCID,
			String description, String serviceURL) {

		String s = "INSERT OR REPLACE INTO real_services (nameID, NFCID, description, serviceURL) "
				+ "VALUES ('"
				+ nameID
				+ "','"
				+ NFCID
				+ "','"
				+ description
				+ "','" + serviceURL + "')";

		dataBase.execSQL(s);

	}

	public void insertUse(List<String> use) {

		Integer latitude = null;
		Integer longitude = null;

		if (use.get(1) != null && use.get(2) != null) {
			latitude = Integer.valueOf(use.get(1));
			longitude = Integer.valueOf(use.get(2));
		}

		insertUse(use.get(0), latitude, longitude, Integer.valueOf(use.get(3)));
	}

	public void insertUse(String NFCID, Integer latitude, Integer longitude,
			Integer type) {
		Date d = new Date();
		insertUse(NFCID, latitude, longitude, d, type);
	}

	public void insertUse(String NFCID, Integer latitude, Integer longitude,
			Date date, Integer type) {
		date.setYear(date.getYear() + 1900);
		long lDate = date.getTime();
		String s = "INSERT OR REPLACE INTO uses (NFCID, latitude, longitude, date, type) "
				+ "VALUES ('"
				+ NFCID
				+ "',"
				+ latitude
				+ ","
				+ longitude
				+ ","
				+ lDate + "," + type + ")";

		dataBase.execSQL(s);

	}

	public void insertUser(Integer userID) {
		String s = "INSERT OR REPLACE INTO uses (userID) " + "VALUES ('"
				+ userID + ")'";

		dataBase.execSQL(s);

	}

	public void insertSecuritiesVirtualService(
			List<List<String>> securitiesVirtualServices) {
		for (List<String> data : securitiesVirtualServices) {
			insertVirtualSecurity(Integer.valueOf(data.get(0)), data.get(1),
					data.get(2), Integer.valueOf(data.get(3)));
		}
	}

	public void insertVirtualSecurity(Integer userID, String nameID,
			String NFCID, Integer securityLevel) {
		String s = "INSERT OR REPLACE INTO virtual_security (userID, nameID, NFCID, securityLevel) "
				+ "VALUES ("
				+ userID
				+ ",'"
				+ nameID
				+ "','"
				+ NFCID
				+ "','"
				+ securityLevel + "')";

		dataBase.execSQL(s);

	}

	public void insertParamsVirtualServices(
			List<List<String>> paramsVirtualServices) {
		for (List<String> data : paramsVirtualServices) {
			insertVirtualParam(data.get(0), data.get(1), data.get(2));
		}
	}

	public void insertVirtualParam(String nameID, String NFCID, String value) {

		String s = "INSERT OR REPLACE INTO virtual_params (nameID, NFCID, value) "
				+ "VALUES ('" + nameID + "','" + NFCID + "','" + value + "')";

		dataBase.execSQL(s);
	}

	public void insertSecuritiesRealService(
			List<List<String>> securitiesRealServices) {
		for (List<String> data : securitiesRealServices) {
			insertVirtualSecurity(Integer.valueOf(data.get(0)), data.get(1),
					data.get(2), Integer.valueOf(data.get(3)));
		}
	}

	public void deleteItem(String nfcId) {
		String s = "DELETE FROM items WHERE NFCID='" + nfcId + "'";
		dataBase.execSQL(s);
	}

	public void insertRealSecurity(Integer userID, String nameID, String NFCID,
			Integer securityLevel) {
		String s = "INSERT OR REPLACE INTO real_security (userID, nameID, NFCID, securityLevel) "
				+ "VALUES ('"
				+ userID
				+ ","
				+ nameID
				+ ","
				+ NFCID
				+ "','"
				+ securityLevel + "')";

		dataBase.execSQL(s);

	}

	public void insertParamsRealServices(List<List<String>> paramsRealServices) {
		for (List<String> data : paramsRealServices) {
			insertRealParam(data.get(0), data.get(1), data.get(2));
		}
	}

	public void insertRealParam(String nameID, String NFCID, String value) {

		String s = "INSERT OR REPLACE INTO real_params (nameID, NFCID, value) "
				+ "VALUES ('" + nameID + "','" + NFCID + "','" + value + "')";

		dataBase.execSQL(s);

	}

	public void insertDatasheet(List<List<String>> datasheet) {
		for (List<String> data : datasheet) {
			insertDatasheet(data.get(0), data.get(1), data.get(2));
		}
	}

	public void insertDatasheet(String NFCID, String key, String value) {
		String s = "INSERT OR REPLACE INTO datasheets (NFCID, key ,value) "
				+ "VALUES ('" + NFCID + "','" + key + "','" + value + "')";

		dataBase.execSQL(s);
	}

	public void insertAll(List<String> item, List<List<String>> datasheet,
			List<List<String>> virtualServices,
			List<List<String>> realServices,
			List<List<String>> paramsVirtualServices,
			List<List<String>> paramsRealServices,
			List<List<String>> securitiesVirtualServices,
			List<List<String>> securitiesRealServices, List<String> region,
			List<String> use) {
		insertRegion(region);
		insertUse(use);
		item.add(region.get(1));
		item.add(region.get(2));
		insertItem(item);
		insertDatasheet(datasheet);
		insertVirtualService(virtualServices);
		insertRealService(realServices);
		insertParamsVirtualServices(paramsVirtualServices);
		insertParamsRealServices(paramsRealServices);
		insertSecuritiesVirtualService(securitiesVirtualServices);
		insertSecuritiesRealService(securitiesRealServices);

	}

	public List<String> getRegionFor(Integer latitude, Integer longitude,
			Integer accuracy) {

		// La lista tiene que llevar en la pos 1 la latitud, en la 2 la longitud
		// y en la 3 el radio de la region a la que pertenece. En caso de no
		// pertenecer a ninguna devuelve la latitud y longitud pasada por
		// parametro
		List<String> res = new ArrayList<String>();

		Location currentLocation = new Location("current");
		currentLocation.setLatitude(latitude / 1E6);
		currentLocation.setLongitude(longitude / 1E6);
		currentLocation.setAccuracy(accuracy);

		// No estan en String.xml pero esq no es tan trivial
		String myRegions = "My Region";
		String name = myRegions + " (1)";

		while (isThereRegionName(name)) {
			String[] sa = name.split(" ");
			String num = sa[sa.length - 1].substring(1, sa.length - 1);
			Integer i = Integer.valueOf(num);
			i++;
			name = myRegions + " (" + i + ")";
		}

		res.add(name);
		res.add(Integer.toString(latitude));
		res.add(Integer.toString(longitude));
		res.add(Integer.toString(accuracy));

		Integer acc = Integer.MAX_VALUE;

		String s = "SELECT * FROM regions";
		List<List<String>> regions = query(s);

		// Name, latitude, longitude, radio
		for (int i = 0; i < regions.size(); i++) {
			List<String> region = regions.get(i);

			Location existingLocation = new Location("existing");

			Integer regionLatitude = Integer.valueOf(region.get(1));
			Integer regionLongitude = Integer.valueOf(region.get(2));
			Integer regionAccuracy = Integer.valueOf(region.get(3));

			existingLocation.setLatitude(regionLatitude / 1E6);
			existingLocation.setLongitude(regionLongitude / 1E6);
			existingLocation.setAccuracy(regionAccuracy);

			Float distance = currentLocation.distanceTo(existingLocation);
			Math.round(distance);

			Integer accuracyError = Math.round(currentLocation.getAccuracy()
					+ existingLocation.getAccuracy());

			if (distance < accuracyError && Math.abs(accuracyError) < acc) {
				res = regions.get(i);
				acc = accuracyError;

			}
		}

		return res;

	}

	public Region getRegion(Integer latitude, Integer longitude) {
		Region region;
		List<List<String>> queryResult = new ArrayList<List<String>>();
		String query = "SELECT * FROM regions WHERE latitude='" + latitude
				+ "' AND longitude='" + longitude + "'";
		queryResult = query(query);
		List<String> regionList = queryResult.get(0);
		region = new RegionImpl(regionList.get(0), Integer.valueOf(regionList
				.get(1)), Integer.valueOf(regionList.get(2)),
				Integer.valueOf(regionList.get(3)));

		return region;
	}

	public Boolean isThereRegionName(String name) {
		String s = "SELECT name FROM regions WHERE name='" + name + "'";
		List<List<String>> lAux = query(s);

		return !lAux.isEmpty();
	}

	public Boolean isThereAliasID(String aliasID) {

		String s = "SELECT aliasID FROM items WHERE aliasID='" + aliasID + "'";
		List<List<String>> lAux = query(s);

		return !lAux.isEmpty();
	}

	public void closeDB() {

		dataBase.close();

	}

	public List<Item> generateItemsList() {
		List<Item> res = new ArrayList<Item>();
		String s = "SELECT * FROM items";
		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			Integer regionLatitude = Integer.valueOf((l.get(7)));
			Integer regionLongitude = Integer.valueOf((l.get(8)));
			List<List<String>> queryResult = new ArrayList<List<String>>();
			String query = "SELECT * FROM regions WHERE latitude='"
					+ regionLatitude + "' AND longitude='" + regionLongitude
					+ "'";
			queryResult = query(query);
			List<String> regionList = queryResult.get(0);
			Region region = new RegionImpl(regionList.get(0),
					Integer.valueOf(regionList.get(1)),
					Integer.valueOf(regionList.get(2)),
					Integer.valueOf(regionList.get(3)));

			// aliasID, NFCID, XMLURL, XMLVersion, manufacturer, model,
			// imageURL, regionLatitude, regionLongitude
			res.add(new ItemImpl(l.get(0), l.get(1), l.get(2), l.get(3), l
					.get(4), l.get(5), l.get(6), region,
					generateVirtualServicesList(l.get(0)),
					generateRealServicesList(l.get(0)), generateDatasheetList(l
							.get(0))));
		}
		return res;
	}

	public List<Item> generateItemsListFromRegion(Region r) {
		List<Item> res = new ArrayList<Item>();
		String s = "";

		if (r == null) {
			s = "SELECT * FROM items";
		} else {

			Integer latitude = r.getLatitude();
			Integer longitude = r.getLongitude();

			if (latitude == null || longitude == null) {
				s = "SELECT * FROM items";
			} else {
				s = "SELECT * FROM items WHERE regionLatitude='" + latitude
						+ "' AND regionLongitude='" + longitude + "'";
			}
		}

		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			// aliasID, NFCID, XMLURL, XMLVersion, manufacturer, model,
			// imageURL, region,
			res.add(new ItemImpl(l.get(0), l.get(1), l.get(2), l.get(3), l
					.get(4), l.get(5), l.get(6), r,
					generateVirtualServicesList(l.get(0)),
					generateRealServicesList(l.get(0)), generateDatasheetList(l
							.get(0))));
		}
		return res;
	}

	public List<VirtualService> generateVirtualServicesList(String NFCID) {
		List<VirtualService> res = new ArrayList<VirtualService>();
		String s = "SELECT * FROM virtual_services WHERE NFCID='" + NFCID + "'";
		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			//
			String s2 = "SELECT * FROM virtual_params WHERE nameID='"
					+ l.get(0) + "' AND NFCID='" + NFCID + "' ";

			List<List<String>> lAux2 = query(s2);
			List<String> params = new ArrayList<String>();
			for (List<String> l2 : lAux2) {
				params.add(l2.get(2));
			}

			res.add(new VirtualServiceImpl(l.get(0), l.get(2), l.get(3), params));
		}
		return res;
	}

	public List<RealService> generateRealServicesList(String NFCID) {
		List<RealService> res = new ArrayList<RealService>();
		String s = "SELECT * FROM real_services WHERE NFCID='" + NFCID + "'";
		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			//
			String s2 = "SELECT * FROM real_params WHERE nameID='" + l.get(0)
					+ "' AND NFCID='" + NFCID + "' ";

			List<List<String>> lAux2 = query(s2);
			List<String> params = new ArrayList<String>();
			for (List<String> l2 : lAux2) {
				params.add(l2.get(2));
			}

			res.add(new RealServiceImpl(l.get(0), l.get(2), l.get(3), params));
		}
		return res;
	}

	public List<Region> generateRegionsList() {
		List<Region> res = new ArrayList<Region>();
		String s = "SELECT * FROM regions";
		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			res.add(new RegionImpl(l.get(0), Integer.valueOf(l.get(1)), Integer
					.valueOf(l.get(2)), Integer.valueOf(l.get(3))));
		}
		return res;
	}

	public List<Region> generateRegion(Integer latitude, Integer longitude) {
		List<Region> res = new ArrayList<Region>();
		List<List<String>> lAux = selectRegion(latitude, longitude);
		for (List<String> l : lAux) {
			res.add(new RegionImpl(l.get(0), Integer.valueOf(l.get(1)), Integer
					.valueOf(l.get(2)), Integer.valueOf(l.get(3))));
		}
		return res;
	}

	public List<DatasheetLine> generateDatasheetList(String NFCID) {
		List<DatasheetLine> res = new ArrayList<DatasheetLine>();
		String s = "SELECT * FROM datasheets WHERE NFCID='" + NFCID + "'";
		List<List<String>> lAux = query(s);
		for (List<String> l : lAux) {
			res.add(new DatasheetLineImpl(l.get(1), l.get(2)));
		}
		return res;
	}

	public void deleteAllTables() {
		dataBase.execSQL("DROP TABLE IF EXISTS real_security");
		dataBase.execSQL("DROP TABLE IF EXISTS virtual_security");
		dataBase.execSQL("DROP TABLE IF EXISTS real_params");
		dataBase.execSQL("DROP TABLE IF EXISTS virtual_params");
		dataBase.execSQL("DROP TABLE IF EXISTS real_services");
		dataBase.execSQL("DROP TABLE IF EXISTS virtual_services");
		dataBase.execSQL("DROP TABLE IF EXISTS uses");
		dataBase.execSQL("DROP TABLE IF EXISTS datasheets");
		dataBase.execSQL("DROP TABLE IF EXISTS items");
		dataBase.execSQL("DROP TABLE IF EXISTS users");
		dataBase.execSQL("DROP TABLE IF EXISTS regions");

	}

	private class DataBaseHelper extends SQLiteOpenHelper {

		private String sqlRegions = "CREATE TABLE regions (name TEXT NOT NULL, latitude INTEGER NOT NULL, longitude INTEGER NOT NULL, radio INTEGER NOT NULL, PRIMARY KEY ( latitude, longitude ))";
		private String sqlUsers = "CREATE TABLE users (userID INTEGER NOT NULL PRIMARY KEY,securityLevel INTEGER NOT NULL)";
		private String sqlItems = "CREATE TABLE items (NFCID TEXT NOT NULL PRIMARY KEY, aliasID TEXT UNIQUE NOT NULL, XMLURL TEXT NOT NULL, XMLVersion INTEGER, manufacturer TEXT NOT NULL, model TEXT NOT NULL, imageURL TEXT, regionLatitude INTEGER NOT NULL, regionLongitude INTEGER NOT NULL, FOREIGN KEY(regionLatitude, regionLongitude) REFERENCES regions(latitude, longitude))";
		private String sqlDatasheets = "CREATE TABLE datasheets (NFCID TEXT NOT NULL, key TEXT NOT NULL, value TEXT, PRIMARY KEY (NFCID, key),FOREIGN KEY(NFCID) REFERENCES items(NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlUses = "CREATE TABLE uses (NFCID TEXT NOT NULL, latitude INTEGER, longitude INTEGER, date NUMERIC NOT NULL,type INTEGER NOT NULL, PRIMARY KEY ( NFCID, date), FOREIGN KEY(NFCID) REFERENCES items(NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlVirtualServices = "CREATE TABLE virtual_services (nameID TEXT NOT NULL, NFCID TEXT NOT NULL, description TEXT, serviceURL TEXT NOT NULL, PRIMARY KEY ( nameID, NFCID ), FOREIGN KEY(NFCID) REFERENCES items(NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlRealServices = "CREATE TABLE real_services (nameID TEXT NOT NULL, NFCID TEXT NOT NULL, description TEXT, serviceURL TEXT, PRIMARY KEY ( nameID, NFCID ), FOREIGN KEY(NFCID) REFERENCES items(NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlVirtualParams = "CREATE TABLE virtual_params (nameID TEXT NOT NULL,NFCID TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (nameID,NFCID,value), FOREIGN KEY(nameID,NFCID) REFERENCES virtual_services(nameID,NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlRealParams = "CREATE TABLE real_params (nameID TEXT NOT NULL,NFCID TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (nameID,NFCID,value), FOREIGN KEY(nameID,NFCID) REFERENCES real_services(nameID,NFCID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlVirtualSecurity = "CREATE TABLE virtual_security (userID INTEGER NOT NULL, nameID TEXT NOT NULL, NFCID TEXT NOT NULL, securityLevel INTEGER NOT NULL,PRIMARY KEY ( userID, nameID, NFCID ), FOREIGN KEY( nameID, NFCID) REFERENCES virtual_services(nameID,NFCID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY( userID ) REFERENCES users (userID) ON DELETE CASCADE ON UPDATE CASCADE)";
		private String sqlRealSecurity = "CREATE TABLE real_security (userID INTEGER NOT NULL, nameID TEXT NOT NULL, NFCID TEXT NOT NULL, securityLevel INTEGER NOT NULL,PRIMARY KEY ( userID, nameID, NFCID ), FOREIGN KEY( nameID, NFCID) REFERENCES real_services(nameID,NFCID) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY( userID ) REFERENCES users (userID) ON DELETE CASCADE ON UPDATE CASCADE)";

		public DataBaseHelper(Context contexto, String nombre,
				CursorFactory factory, int version) {

			super(contexto, nombre, factory, version);

		}

		@Override
		public void onCreate(SQLiteDatabase database) {
			// Se ejecuta la sentencia SQL de creaci�n de la tabla
			database.execSQL(sqlRegions);
			database.execSQL(sqlUsers);
			database.execSQL(sqlItems);
			database.execSQL(sqlDatasheets);
			database.execSQL(sqlUses);
			database.execSQL(sqlVirtualServices);
			database.execSQL(sqlRealServices);
			database.execSQL(sqlVirtualParams);
			database.execSQL(sqlRealParams);
			database.execSQL(sqlVirtualSecurity);
			database.execSQL(sqlRealSecurity);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int versionAnterior,
				int versionNueva) {

			db.execSQL("DROP TABLE IF EXISTS real_security");
			db.execSQL("DROP TABLE IF EXISTS virtual_security");
			db.execSQL("DROP TABLE IF EXISTS real_params");
			db.execSQL("DROP TABLE IF EXISTS virtual_params");
			db.execSQL("DROP TABLE IF EXISTS real_services");
			db.execSQL("DROP TABLE IF EXISTS virtual_services");
			db.execSQL("DROP TABLE IF EXISTS uses");
			db.execSQL("DROP TABLE IF EXISTS items");
			db.execSQL("DROP TABLE IF EXISTS datasheets");
			db.execSQL("DROP TABLE IF EXISTS users");
			db.execSQL("DROP TABLE IF EXISTS regions");

			db.execSQL(sqlUsers);
			db.execSQL(sqlRegions);
			db.execSQL(sqlItems);
			db.execSQL(sqlDatasheets);
			db.execSQL(sqlUses);
			db.execSQL(sqlVirtualServices);
			db.execSQL(sqlRealServices);
			db.execSQL(sqlVirtualParams);
			db.execSQL(sqlRealParams);
			db.execSQL(sqlVirtualSecurity);
			db.execSQL(sqlRealSecurity);

		}

	}

	// Metodos que realizan colsultas/modificaiones a la base de datos

	// Estadisticas del Item
	public Date getItemLastUse(String NFCID) {
		String s = "SELECT max(date) FROM uses WHERE NFCID='" + NFCID
				+ "' AND (type=" + MyApplication.VIRTUAL + " OR type="
				+ MyApplication.REAL + ")";
		List<List<String>> list = query(s);

		Long largo = 0L;
		try {
			largo = Long.valueOf(list.get(0).get(0));
		} catch (Exception e) {
			s = "SELECT max(date) FROM uses WHERE NFCID='" + NFCID
					+ "' AND type=" + MyApplication.TAGGED;
			list.clear();
			list = query(s);
			largo = Long.valueOf(list.get(0).get(0));
		}
		Date da = new Date(0);
		da.setTime(largo);

		return da;
	}

	public Integer getItemNumUses(String NFCID) {
		String s = "SELECT count(*) FROM uses WHERE NFCID='" + NFCID + "'";
		List<List<String>> list = query(s);
		Integer in = Integer.valueOf(list.get(0).get(0));
		return in;

	}

	public List<String> getItemLastRegion(String NFCID) {

		String s = "SELECT name, regionLatitude, regionLongitude FROM regions, items WHERE NFCID='"
				+ NFCID
				+ "' AND regionLatitude=latitude AND regionLongitude=longitude";
		List<List<String>> list = query(s);

		return list.get(0);
	}

	public Date getItemRegistrationDate(String NFCID) {
		String s = "SELECT min(date) FROM uses WHERE NFCID='" + NFCID + "'";
		List<List<String>> list = query(s);
		Date da = new Date(0);

		Long largo = Long.valueOf(list.get(0).get(0));
		da.setTime(largo);

		return da;
	}

	public boolean isItemInDataBase(String tagID) {
		String s = "SELECT NFCID FROM items WHERE NFCID='" + tagID + "'";
		List<List<String>> lAux = query(s);

		return !lAux.isEmpty();
	}

	// Estadisticas generales

	public String getMostUsed() {
		String s = "SELECT aliasID FROM items i,uses u WHERE i.NFCID=u.NFCID GROUP BY i.aliasID ORDER BY count(*) DESC LIMIT 1";
		List<List<String>> list = query(s);
		String str = list.get(0).get(0);

		return str;
	}

	public String getLessUsed() {
		String s = "SELECT aliasID FROM items i,uses u WHERE i.NFCID=u.NFCID GROUP BY i.aliasID ORDER BY count(*) ASC LIMIT 1";
		List<List<String>> list = query(s);
		String str = list.get(0).get(0);

		return str;
	}

	public Integer getNumTimesTagged() {

		String s = "SELECT count(*) FROM uses WHERE type="
				+ MyApplication.TAGGED;
		List<List<String>> list = query(s);
		Integer num = Integer.valueOf(list.get(0).get(0));
		return num;
	}

	public Integer getNumServicesUsed() {

		String s = "SELECT count(*) FROM uses WHERE type="
				+ MyApplication.VIRTUAL + " OR type=" + MyApplication.REAL;
		List<List<String>> list = query(s);
		Integer num = Integer.valueOf(list.get(0).get(0));
		return num;

	}

	public Integer getNumItems() {

		String s = "SELECT count(aliasID) FROM items";
		List<List<String>> list = query(s);
		Integer num = Integer.valueOf(list.get(0).get(0));
		return num;
	}

	public Boolean oneRegion(Integer latitude, Integer longitude,
			Integer accuracy) {
		return true;
	}

}
